package com.gl.portal.system.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import com.gl.portal.system.entity.SysDept;

public interface SysDeptRepository extends JpaRepository<SysDept, Long>, JpaSpecificationExecutor<SysDept> {

	List<SysDept> findByIdIn(List<Long> ids);

	/**
	 * 根据角色ID查询部门树信息
	 *
	 * @param roleId
	 * @return
	 */
	@Query(value = "SELECT d.id, d.parent_id \n" + //
				   "FROM sys_dept d \n" + //
				   "    LEFT JOIN sys_role_dept rd ON d.id = rd.dept_id \n" + //
				   "WHERE rd.role_id = ?1 \n" + //
				   " 	AND d.id NOT IN (SELECT d.parent_id FROM sys_dept d INNER JOIN sys_role_dept rd ON d.id = rd.dept_id AND rd.role_id = ?1) \n" + //
				   "ORDER BY d.parent_id, d.sort_num", nativeQuery = true)
	List<SysDept> selectDeptListByRoleId(Long roleId);

	List<SysDept> findByParentIdIn(List<Long> parentIds);

	/**
	 * 查询子节点数
	 *
	 * @param parentId
	 * @param isDeleted
	 * @return
	 */
	int countByParentIdAndIsDeleted(Long parentId, Integer isDeleted);

	@Query(value = "SELECT * FROM sys_dept WHERE dept_name = ?1 AND parent_id = ?2 AND is_deleted = 0", nativeQuery = true)
	SysDept findByDeptNameAndParentId(String deptName, Long parentId);

	/**
	 * 查询子部门集合
	 *
	 * @param id
	 * @return
	 */
	@Query(value = "SELECT * FROM sys_dept WHERE FIND_IN_SET(?1, ancestors)", nativeQuery = true)
	List<SysDept> selectChildrenDeptById(Long id);

}
